package com.bayleaf.web.stringMatching.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class StringMatchingDAO {
	private Connection con;
	private PreparedStatement stmt = null; 
	private ResultSet rs = null;  

	private String url = "jdbc:hsqldb:file:F:\\SSAProject\\bayleaf\\db\\hsqldb"; 
	private String user = "sa"; 
	private String password = ""; 
	
	
	public StringMatchingDAO(){
		hsqlDBConnection();
	}


	
	public ArrayList<StringMatchingVO> getMarchedIdNameSetById(String id){
		ArrayList<StringMatchingVO> matchedNameArray = new ArrayList<StringMatchingVO>();
	    
		String sQuery = "SELECT ID, NAME FROM BOOK WHERE ID LIKE ?"; 

	    try {
			stmt = con.prepareStatement(sQuery);
			stmt.setString(1, id+"%");
			rs = stmt.executeQuery();
			
			while (rs.next()) { 
				StringMatchingVO vo = new StringMatchingVO();
				vo.setId(rs.getString(1));
				vo.setName(rs.getString(2));		
				matchedNameArray.add(vo);
			  }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally { 
         if (rs != null) try {rs.close(); } catch (Exception ex) {} 
         if (stmt != null) try {stmt.close(); } catch (Exception ex) {} 
         //if (con != null) try {con.close(); } catch (Exception ex) {} 
		} 

		return matchedNameArray;
	}

	public ArrayList<StringMatchingVO> getMarchedIdNameSetByName(String name){
		ArrayList<StringMatchingVO> matchedNameArray = new ArrayList<StringMatchingVO>();
	    
		String sQuery = "SELECT ID, NAME FROM BOOK WHERE NAME LIKE ?"; 

	    try {
			stmt = con.prepareStatement(sQuery);
			stmt.setString(1, name+"%");
			rs = stmt.executeQuery();
			
			while (rs.next()) { 
				StringMatchingVO vo = new StringMatchingVO();
				vo.setId(rs.getString(1));
				vo.setName(rs.getString(2));		
				matchedNameArray.add(vo);
			  }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally { 
         if (rs != null) try {rs.close(); } catch (Exception ex) {} 
         if (stmt != null) try {stmt.close(); } catch (Exception ex) {} 
         //if (con != null) try {con.close(); } catch (Exception ex) {} 
		} 

		return matchedNameArray;
	}

	public void hsqlDBConnection(){
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			con = DriverManager.getConnection(url, user, password);
	 
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		System.out.println(con);
	}
	
	
	public void hsqlDBClose(){
		  try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
